package util;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 用户数据访问对象类，处理用户数据的增删改查
 */
public class UserDAO {

    // 注册新用户
    public boolean registerUser(User user) {
        String sql = "INSERT INTO users (username, password, name, contact, region, points, carbon_reduction) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?)";

        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getPassword());
            pstmt.setString(3, user.getName());
            pstmt.setString(4, user.getContact());
            pstmt.setString(5, user.getRegion());
            pstmt.setInt(6, 0);
            pstmt.setDouble(7, 0.0);

            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
            return false;
        } finally {
            DatabaseUtil.closeConnection(conn, pstmt, null);
        }
    }

    // 用户登录验证
    public User login(String username, String password) {
        String sql = "SELECT * FROM users WHERE username = ? AND password = ?";

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, username);
            pstmt.setString(2, password);

            rs = pstmt.executeQuery();

            if (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setName(rs.getString("name"));
                user.setContact(rs.getString("contact"));
                user.setRegion(rs.getString("region"));
                user.setPoints(rs.getInt("points"));
                user.setCarbonReduction(rs.getDouble("carbon_reduction"));

                // 读取新增字段
                try {
                    user.setGender(rs.getString("gender"));
                    user.setBirthDate(rs.getString("birth_date"));
                    user.setEmail(rs.getString("email"));
                    user.setHobbies(rs.getString("hobbies"));
                    user.setPreferredTravelMode(rs.getString("preferred_travel_mode"));
                    user.setProfilePublic(rs.getBoolean("profile_public"));
                } catch (SQLException e) {
                    // 如果数据库中没有这些字段，忽略异常
                    System.out.println("注意：数据库中可能缺少部分用户扩展字段");
                }

                return user;
            }
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeConnection(conn, pstmt, rs);
        }

        return null;
    }

    // 更新用户信息
    public boolean updateUser(User user) {
        String sql = "UPDATE users SET name = ?, contact = ?, region = ?, gender = ?, birth_date = ?, " +
                "email = ?, hobbies = ?, preferred_travel_mode = ?, profile_public = ? WHERE id = ?";

        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, user.getName());
            pstmt.setString(2, user.getContact());
            pstmt.setString(3, user.getRegion());
            pstmt.setString(4, user.getGender());
            pstmt.setString(5, user.getBirthDate());
            pstmt.setString(6, user.getEmail());
            pstmt.setString(7, user.getHobbies());
            pstmt.setString(8, user.getPreferredTravelMode());
            pstmt.setBoolean(9, user.isProfilePublic());
            pstmt.setInt(10, user.getId());

            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
            return false;
        } finally {
            DatabaseUtil.closeConnection(conn, pstmt, null);
        }
    }

    // 更新用户积分和碳减排量
    public boolean updatePointsAndCarbonReduction(int userId, int points, double carbonReduction) {
        String sql = "UPDATE users SET points = points + ?, carbon_reduction = carbon_reduction + ? WHERE id = ?";

        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, points);
            pstmt.setDouble(2, carbonReduction);
            pstmt.setInt(3, userId);

            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
            return false;
        } finally {
            DatabaseUtil.closeConnection(conn, pstmt, null);
        }
    }

    // 获取碳减排排行榜
    public List<User> getCarbonReductionRanking() {
        String sql = "SELECT id, username, name, carbon_reduction FROM users ORDER BY carbon_reduction DESC";

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<User> ranking = new ArrayList<>();

        try {
            conn = DatabaseUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setName(rs.getString("name"));
                user.setCarbonReduction(rs.getDouble("carbon_reduction"));

                ranking.add(user);
            }
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeConnection(conn, stmt, rs);
        }

        return ranking;
    }

    // 验证用户联系方式
    public boolean verifyUserContact(String username, String contact) {
        String sql = "SELECT * FROM users WHERE username = ? AND contact = ?";

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, username);
            pstmt.setString(2, contact);

            rs = pstmt.executeQuery();

            return rs.next(); // 返回是否找到匹配记录
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
            return false;
        } finally {
            DatabaseUtil.closeConnection(conn, pstmt, rs);
        }
    }

    // 重置用户密码
    public boolean resetPassword(String username, String contact, String newPassword) {
        String sql = "UPDATE users SET password = ? WHERE username = ? AND contact = ?";

        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, newPassword);
            pstmt.setString(2, username);
            pstmt.setString(3, contact);

            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
            return false;
        } finally {
            DatabaseUtil.closeConnection(conn, pstmt, null);
        }
    }

    // 根据ID获取用户信息
    public User getUserById(int userId) {
        String sql = "SELECT * FROM users WHERE id = ?";

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);

            rs = pstmt.executeQuery();

            if (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setName(rs.getString("name"));
                user.setContact(rs.getString("contact"));
                user.setRegion(rs.getString("region"));
                user.setPoints(rs.getInt("points"));
                user.setCarbonReduction(rs.getDouble("carbon_reduction"));

                // 读取新增字段
                try {
                    user.setGender(rs.getString("gender"));
                    user.setBirthDate(rs.getString("birth_date"));
                    user.setEmail(rs.getString("email"));
                    user.setHobbies(rs.getString("hobbies"));
                    user.setPreferredTravelMode(rs.getString("preferred_travel_mode"));
                    user.setProfilePublic(rs.getBoolean("profile_public"));
                } catch (SQLException e) {
                    // 如果数据库中没有这些字段，忽略异常
                    System.out.println("注意：数据库中可能缺少部分用户扩展字段");
                }

                return user;
            }
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeConnection(conn, pstmt, rs);
        }

        return null;
    }

    // 更新用户密码
    public boolean updateUserPassword(int userId, String newPassword) {
        String sql = "UPDATE users SET password = ? WHERE id = ?";

        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, newPassword);
            pstmt.setInt(2, userId);

            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
            return false;
        } finally {
            DatabaseUtil.closeConnection(conn, pstmt, null);
        }
    }
}